import json
import logging
from flask import Blueprint, request, jsonify
from app.utils.logger import log_business_logic
from app.services.starrocks_service import starrocks_service
from app.utils.mysql_db import get_db_cursor

starrocks_tables_bp = Blueprint('starrocks_tables', __name__, url_prefix='/api/starrocks-tables')
logger = logging.getLogger(__name__)


@starrocks_tables_bp.route('/configs', methods=['GET'])
@log_business_logic("获取StarRocks配置列表")
def get_configs_api():
    """获取StarRocks配置列表（用于前端下拉选择）"""
    try:
        with get_db_cursor() as cursor:
            cursor.execute("""
                SELECT id, config_name, host, port, username, `database`, description
                FROM service_config 
                WHERE service_type = 'starrocks' AND is_deleted = 0 AND is_active = 1
                ORDER BY sort_order, id
            """)
            configs = cursor.fetchall()
            return jsonify({
                "success": True,
                "data": configs
            })
    except Exception as e:
        logger.error(f"获取配置列表失败: {e}")
        return jsonify({"error": str(e)}), 500


@starrocks_tables_bp.route('/tables', methods=['GET'])
@log_business_logic("获取StarRocks表列表")
def get_tables_api():
    """获取表列表API - 支持排序和筛选"""
    try:
        config_id = request.args.get('config_id', type=int)
        if not config_id:
            return jsonify({"error": "缺少参数: config_id"}), 400
        
        page = request.args.get('page', 1, type=int)
        page_size = request.args.get('pageSize', 20, type=int)
        search = request.args.get('search', '').strip()
        database = request.args.get('database', '').strip()
        monitor_enabled = request.args.get('monitor_enabled', '').strip()
        storage_medium = request.args.get('storage_medium', '').strip()
        is_partitioned = request.args.get('is_partitioned', '').strip()
        sort = request.args.get('sort', '').strip()
        order = request.args.get('order', '').strip()
        
        result = starrocks_service.get_tables(
            config_id=config_id,
            page=page, page_size=page_size, search=search, 
            database=database, monitor_enabled=monitor_enabled,
            storage_medium=storage_medium, is_partitioned=is_partitioned,
            sort=sort, order=order
        )
        return jsonify(result)
    except Exception as e:
        logger.error(f"❌ 获取表列表失败: {e}")
        return jsonify({"error": f"获取表列表失败: {str(e)}"}), 500


@starrocks_tables_bp.route('/tables/<int:table_id>', methods=['GET'])
@log_business_logic("获取StarRocks表详情")
def get_table_detail_api(table_id):
    """获取表详情API"""
    try:
        result = starrocks_service.get_table_detail(table_id)
        return jsonify(result)
    except Exception as e:
        logger.error(f"❌ 获取表详情失败: {e}")
        return jsonify({"error": f"获取表详情失败: {str(e)}"}), 500


@starrocks_tables_bp.route('/tables/<int:table_id>/monitor', methods=['PUT'])
@log_business_logic("切换StarRocks表监控状态")
def toggle_table_monitor_api(table_id):
    """切换表监控状态API"""
    try:
        data = request.get_json()
        monitor_enabled = data.get('monitor_enabled', True)
        result = starrocks_service.toggle_monitor_status(table_id, monitor_enabled)
        return jsonify(result)
    except Exception as e:
        logger.error(f"❌ 切换表监控状态失败: {e}")
        return jsonify({"error": f"切换监控状态失败: {str(e)}"}), 500


@starrocks_tables_bp.route('/tables/<int:table_id>/count', methods=['POST'])
@log_business_logic("手动COUNT统计表数据")
def count_table_data_api(table_id):
    """手动COUNT统计表数据API"""
    try:
        data = request.get_json() or {}
        config_id = data.get('config_id') or request.args.get('config_id', type=int)
        if not config_id:
            return jsonify({"error": "缺少参数: config_id"}), 400
        
        result = starrocks_service.count_table_data(config_id, table_id)
        return jsonify(result)
    except Exception as e:
        logger.error(f"❌ COUNT统计失败: {e}")
        return jsonify({"error": f"COUNT统计失败: {str(e)}"}), 500


@starrocks_tables_bp.route('/tables/<int:table_id>/stats/history', methods=['GET'])
@log_business_logic("获取表统计历史")
def get_table_stats_history_api(table_id):
    """获取表统计历史API"""
    try:
        # 获取查询参数
        days = request.args.get('days', 30, type=int)  # 默认30天
        stat_type = request.args.get('stat_type', '').strip()  # 统计类型筛选
        
        with get_db_cursor() as cursor:
            # 构建查询条件
            where_conditions = ["table_id = %s"]
            params = [table_id]
            
            # 时间范围
            where_conditions.append("stat_time >= DATE_SUB(NOW(), INTERVAL %s DAY)")
            params.append(days)
            
            # 统计类型筛选
            if stat_type:
                where_conditions.append("stat_type = %s")
                params.append(stat_type)
            
            where_clause = " AND ".join(where_conditions)
            
            # 查询统计历史（三增量设计）
            cursor.execute(f"""
                SELECT 
                    id,
                    database_name,
                    table_name,
                    stat_time,
                    stat_type,
                    total_rows,
                    data_length,
                    index_length,
                    previous_increment_rows,
                    current_increment_rows,
                    total_increment_rows,
                    previous_increment_data,
                    current_increment_data,
                    total_increment_data,
                    collection_method,
                    collection_duration_ms
                FROM starrocks_table_stats
                WHERE {where_clause}
                ORDER BY stat_time DESC
                LIMIT 1000
            """, params)
            
            stats = cursor.fetchall()
            
            # 转换时间格式
            for stat in stats:
                if stat.get('stat_time'):
                    stat['stat_time'] = stat['stat_time'].strftime('%Y-%m-%d %H:%M:%S')
            
            return jsonify({
                'success': True,
                'data': {
                    'stats': stats,
                    'count': len(stats)
                }
            })
    except Exception as e:
        logger.error(f"❌ 获取统计历史失败: {e}")
        return jsonify({"error": f"获取统计历史失败: {str(e)}"}), 500


@starrocks_tables_bp.route('/stats/overview', methods=['GET'])
@log_business_logic("获取StarRocks表管理总体统计")
def get_overview_stats_api():
    """获取总体统计信息API"""
    try:
        config_id = request.args.get('config_id', type=int)
        if not config_id:
            return jsonify({"error": "缺少参数: config_id"}), 400
        
        result = starrocks_service.get_overview_stats(config_id)
        return jsonify(result)
    except Exception as e:
        logger.error(f"❌ 获取总体统计失败: {e}")
        return jsonify({"error": f"获取总体统计失败: {str(e)}"}), 500


@starrocks_tables_bp.route('/databases', methods=['GET'])
@log_business_logic("获取StarRocks数据库列表")
def get_databases_api():
    """获取数据库列表API"""
    try:
        config_id = request.args.get('config_id', type=int)
        if not config_id:
            return jsonify({"error": "缺少参数: config_id"}), 400
        
        db_stats = starrocks_service.get_databases(config_id)
        return jsonify({'databases': db_stats})
    except Exception as e:
        logger.error(f"❌ 获取数据库列表失败: {e}")
        return jsonify({"error": f"获取数据库列表失败: {str(e)}"}), 500

@starrocks_tables_bp.route('/databases/<database>/sync', methods=['POST'])
@log_business_logic("同步StarRocks数据库表结构")
def sync_database_tables_api(database):
    """同步数据库表结构API"""
    try:
        data = request.get_json() or {}
        config_id = data.get('config_id')
        if not config_id:
            return jsonify({"error": "缺少参数: config_id"}), 400
        
        result = starrocks_service.sync_database_tables(config_id, database)
        return jsonify({'success': True, 'data': result})
    except Exception as e:
        logger.error(f"❌ 同步数据库表结构失败: {e}")
        return jsonify({"error": f"同步失败: {str(e)}"}), 500


@starrocks_tables_bp.route('/monitor/config', methods=['GET'])
@log_business_logic("获取监控配置")
def get_monitor_config_api():
    """获取监控配置API"""
    try:
        configs = starrocks_service.get_monitor_config()
        return jsonify({"success": True, "data": {"configs": configs}})
    except Exception as e:
        logger.error(f"❌ 获取监控配置失败: {e}")
        return jsonify({"error": f"获取配置失败: {str(e)}"}), 500


@starrocks_tables_bp.route('/monitor/config', methods=['PUT'])
@log_business_logic("更新监控配置")
def update_monitor_config_api():
    """更新监控配置API"""
    try:
        data = request.get_json()
        configs_list = data.get('configs', [])
        if not configs_list:
            return jsonify({"error": "没有提供配置更新数据"}), 400
        
        # 将数组格式转换为字典格式
        # 前端传递: [{config_key: 'xxx', config_value: 'yyy'}, ...]
        # 转换为: {xxx: yyy, ...}
        config_updates = {}
        for config in configs_list:
            if isinstance(config, dict) and 'config_key' in config and 'config_value' in config:
                config_updates[config['config_key']] = config['config_value']
        
        if not config_updates:
            return jsonify({"error": "配置数据格式错误"}), 400
            
        result = starrocks_service.update_monitor_config(config_updates)
        return jsonify(result)
    except Exception as e:
        logger.error(f"❌ 更新监控配置失败: {e}")
        return jsonify({"error": f"更新配置失败: {str(e)}"}), 500


@starrocks_tables_bp.route('/one-click-update', methods=['POST'])
@log_business_logic("一键更新")
def one_click_update_api():
    """一键更新API"""
    try:
        data = request.get_json() or {}
        config_id = data.get('config_id')
        if not config_id:
            return jsonify({"error": "缺少参数: config_id"}), 400
        
        result = starrocks_service.one_click_update(config_id)
        return jsonify(result)
    except Exception as e:
        logger.error(f"❌ 一键更新失败: {e}")
        return jsonify({"error": f"一键更新失败: {str(e)}"}), 500


@starrocks_tables_bp.route('/fetch-latest', methods=['POST'])
@log_business_logic("获取最新表")
def fetch_latest_tables_api():
    """获取最新表API"""
    try:
        data = request.get_json() or {}
        config_id = data.get('config_id')
        if not config_id:
            return jsonify({"error": "缺少参数: config_id"}), 400
        
        result = starrocks_service.fetch_latest_tables(config_id)
        return jsonify({"success": True, "data": result})
    except Exception as e:
        logger.error(f"❌ 获取最新表失败: {e}")
        return jsonify({"error": f"获取最新表失败: {str(e)}"}), 500


@starrocks_tables_bp.route('/statistics/execute', methods=['POST'])
@log_business_logic("执行数据统计")
def execute_data_statistics_api():
    """执行数据统计API"""
    try:
        data = request.get_json()
        config_id = data.get('config_id')
        if not config_id:
            return jsonify({"error": "缺少参数: config_id"}), 400
        
        database = data.get('database', '')
        collection_method = data.get('method', data.get('collection_method', 'information_schema'))
        selected_table_ids = data.get('tables', data.get('selected_table_ids', []))
        stat_type = data.get('stat_type', 'manual')  # 支持传递stat_type参数
        result = starrocks_service.execute_data_statistics(config_id, database, collection_method, selected_table_ids, stat_type)
        
        # 判断统计结果：如果所有表都失败，返回失败状态
        success_count = result.get('success_count', 0)
        failed_count = result.get('failed_count', 0)
        total_count = success_count + failed_count
        
        if total_count > 0 and success_count == 0:
            # 所有表都失败
            return jsonify({
                "success": False, 
                "message": f"统计失败：{failed_count}个表全部失败",
                "data": result
            }), 400
        elif failed_count > 0:
            # 部分失败
            return jsonify({
                "success": True, 
                "message": f"统计完成：成功{success_count}个，失败{failed_count}个",
                "data": result
            })
        else:
            # 全部成功
            return jsonify({
                "success": True, 
                "message": f"统计成功：{success_count}个表",
                "data": result
            })
    except Exception as e:
        logger.error(f"❌ 执行数据统计失败: {e}")
        return jsonify({"success": False, "error": f"执行数据统计失败: {str(e)}"}), 500


@starrocks_tables_bp.route('/monitor/batch-toggle', methods=['POST'])
@log_business_logic("执行监控操作")
def execute_monitor_action_api():
    """执行监控操作API"""
    try:
        data = request.get_json()
        config_id = data.get('config_id')
        if not config_id:
            return jsonify({"error": "缺少参数: config_id"}), 400
        
        database = data.get('database', '')
        table_ids = data.get('tables', data.get('table_ids', []))
        enable = data.get('monitor_enabled', data.get('enable', True))
        
        # 如果没有指定表ID，但指定了数据库，则获取该数据库的所有表ID
        if not table_ids and database is not None:
            with get_db_cursor() as cursor:
                if database:
                    # 指定数据库（添加config_id过滤）
                    cursor.execute("SELECT id FROM starrocks_tables WHERE config_id = %s AND database_name = %s", (config_id, database))
                else:
                    # 全部数据库（添加config_id过滤）
                    cursor.execute("SELECT id FROM starrocks_tables WHERE config_id = %s", (config_id,))
                table_records = cursor.fetchall()
                table_ids = [record['id'] for record in table_records]
        
        result = starrocks_service.execute_monitor_action(table_ids, enable)
        return jsonify({"success": True, "data": result})
    except Exception as e:
        logger.error(f"❌ 执行监控操作失败: {e}")
        return jsonify({"error": f"执行监控操作失败: {str(e)}"}), 500


@starrocks_tables_bp.route('/schema/batch-update', methods=['POST'])
@log_business_logic("执行表结构更新")
def execute_schema_update_api():
    """执行表结构更新API"""
    try:
        data = request.get_json()
        config_id = data.get('config_id')
        if not config_id:
            return jsonify({"error": "缺少参数: config_id"}), 400
        
        database = data.get('database', '')
        selected_table_ids = data.get('selected_table_ids', data.get('tables', []))
        
        # 如果没有指定表ID，但指定了数据库，则获取该数据库的所有表ID
        if not selected_table_ids and database is not None:
            with get_db_cursor() as cursor:
                if database:
                    cursor.execute("SELECT id FROM starrocks_tables WHERE config_id = %s AND database_name = %s AND monitor_enabled = 1", (config_id, database))
                else:
                    cursor.execute("SELECT id FROM starrocks_tables WHERE config_id = %s AND monitor_enabled = 1", (config_id,))
                table_records = cursor.fetchall()
                selected_table_ids = [record['id'] for record in table_records]
        
        result = starrocks_service.execute_schema_update(config_id, database, selected_table_ids)
        return jsonify({"success": True, "data": result})
    except Exception as e:
        logger.error(f"❌ 执行表结构更新失败: {e}")
        return jsonify({"error": f"执行表结构更新失败: {str(e)}"}), 500


@starrocks_tables_bp.route('/tables/<int:table_id>/generate-query', methods=['GET'])
@log_business_logic("生成查询语句")
def generate_query_statement_api(table_id):
    """生成查询语句API"""
    try:
        result = starrocks_service.generate_query_statement(table_id)
        return jsonify(result)
    except Exception as e:
        logger.error(f"❌ 生成查询语句失败: {e}")
        return jsonify({"error": f"生成查询语句失败: {str(e)}"}), 500

# 添加缺失的API端点

@starrocks_tables_bp.route('/stats/collect', methods=['POST'])
@log_business_logic("收集统计数据")
def collect_stats_api():
    """收集统计数据API"""
    try:
        data = request.get_json()
        
        # 获取必需的参数
        config_id = data.get('config_id')
        if not config_id:
            return jsonify({"error": "缺少config_id参数"}), 400
        
        # 获取可选参数
        method = data.get('method', 'information_schema')
        stat_type = data.get('stat_type', 'manual')
        database = data.get('database', '')  # 空字符串表示所有数据库
        selected_table_ids = data.get('selected_table_ids', [])  # 空列表表示所有表
        
        # 调用统计功能
        result = starrocks_service.execute_data_statistics(
            config_id=config_id,
            database=database,
            collection_method=method,
            selected_table_ids=selected_table_ids,
            stat_type=stat_type
        )
        
        return jsonify({'success': True, 'data': result})
    except Exception as e:
        logger.error(f"❌ 收集统计数据失败: {e}")
        logger.exception(e)  # 打印完整堆栈
        return jsonify({"error": f"收集统计数据失败: {str(e)}"}), 500

@starrocks_tables_bp.route('/monitor/status', methods=['GET'])
@log_business_logic("获取监控状态")
def get_monitor_status_api():
    """获取监控状态API - 返回真实的调度器状态"""
    try:
        from app.services.scheduler_service import get_scheduler
        
        # 获取真实的调度器状态
        scheduler = get_scheduler()
        status_data = scheduler.get_job_status() if scheduler else {
            'scheduler_running': False,
            'jobs': []
        }
        
        # 查询被监控的表数量
        monitored_count = 0
        conn = None
        try:
            conn = get_db_connection()
            with conn.cursor() as cur:
                cur.execute("SELECT COUNT(*) as count FROM starrocks_table_management WHERE monitor_enabled = 1")
                result = cur.fetchone()
                monitored_count = result['count'] if result else 0
        except:
            pass
        finally:
            if conn:
                conn.close()
        
        # 返回统一的状态格式
        result = {
            'running': status_data.get('scheduler_running', False),
            'jobs': status_data.get('jobs', []),
            'total_jobs': status_data.get('total_jobs', 0),
            'monitored_tables_count': monitored_count,
            'message': '调度器运行中' if status_data.get('scheduler_running') else '调度器已停止'
        }
        
        return jsonify({'success': True, 'data': result})
    except Exception as e:
        logger.error(f"❌ 获取监控状态失败: {e}")
        return jsonify({'success': False, "error": f"获取监控状态失败: {str(e)}"}), 500

@starrocks_tables_bp.route('/monitor/start', methods=['POST'])
@log_business_logic("启动监控服务")
def start_monitor_api():
    """启动监控服务API - 启动调度器"""
    try:
        from app.services.scheduler_service import start_scheduler
        
        # 启动调度器
        scheduler = start_scheduler()
        status_data = scheduler.get_job_status()
        
        result = {
            'running': status_data.get('scheduler_running', False),
            'jobs': status_data.get('jobs', []),
            'total_jobs': status_data.get('total_jobs', 0),
            'message': '调度器启动成功'
        }
        
        return jsonify({'success': True, 'data': result})
    except Exception as e:
        logger.error(f"❌ 启动调度器失败: {e}")
        return jsonify({'success': False, "error": f"启动调度器失败: {str(e)}"}), 500

@starrocks_tables_bp.route('/monitor/stop', methods=['POST'])
@log_business_logic("停止监控服务")
def stop_monitor_api():
    """停止监控服务API - 停止调度器"""
    try:
        from app.services.scheduler_service import get_scheduler
        
        # 停止调度器
        scheduler = get_scheduler()
        if scheduler:
            scheduler.shutdown()
            
        result = {
            'running': False,
            'jobs': [],
            'total_jobs': 0,
            'message': '调度器已停止'
        }
        
        return jsonify({'success': True, 'data': result})
    except Exception as e:
        logger.error(f"❌ 停止调度器失败: {e}")
        return jsonify({'success': False, "error": f"停止调度器失败: {str(e)}"}), 500

@starrocks_tables_bp.route('/sql-parser/table-fields/<database>/<table_name>', methods=['GET'])
@log_business_logic("获取表字段信息")
def get_table_fields_api(database, table_name):
    """获取表字段信息API"""
    try:
        from app.utils.starrocks_db import get_starrocks_cursor
        # 从StarRocks查询表字段信息
        with get_starrocks_cursor() as cursor:
            cursor.execute("""
                SELECT 
                    COLUMN_NAME as field_name,
                    DATA_TYPE as data_type,
                    IS_NULLABLE as nullable,
                    COLUMN_DEFAULT as default_value,
                    COLUMN_COMMENT as comment
                FROM information_schema.COLUMNS 
                WHERE TABLE_SCHEMA = %s AND TABLE_NAME = %s
                ORDER BY ORDINAL_POSITION
            """, (database, table_name))
            fields = cursor.fetchall()
        
        result = {
            'database': database,
            'table_name': table_name,
            'fields': fields
        }
        return jsonify({'success': True, 'data': result})
    except Exception as e:
        logger.error(f"❌ 获取表字段信息失败: {e}")
        return jsonify({"error": f"获取表字段信息失败: {str(e)}"}), 500

@starrocks_tables_bp.route('/sql-parser/generate', methods=['POST'])
@log_business_logic("生成SQL查询语句")
def generate_sql_query_api():
    """生成SQL查询语句API"""
    try:
        data = request.get_json()
        table_name = data.get('table_name', '')
        fields = data.get('fields', [])
        schema_name = data.get('schema_name', '')
        
        # 构建SELECT查询语句
        if fields and len(fields) > 0:
            # 处理两种情况：字符串列表或字典列表
            if isinstance(fields[0], dict):
                field_names = [f['field_name'] for f in fields]
            else:
                field_names = fields  # 已经是字符串列表
            fields_str = ', '.join(field_names)
        else:
            fields_str = '*'
        
        if schema_name:
            full_table_name = f"{schema_name}.{table_name}"
        else:
            full_table_name = table_name
            
        select_query = f"SELECT {fields_str}\nFROM {full_table_name}\nLIMIT 100;"
        
        result = {
            'select_query': select_query,
            'field_count': len(fields) if fields else 0,
            'table_name': table_name
        }
        return jsonify({'success': True, 'data': result})
    except Exception as e:
        logger.error(f"❌ 生成SQL查询语句失败: {e}")
        return jsonify({"error": f"生成SQL查询语句失败: {str(e)}"}), 500